package xgenerator.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

import xgenerator.constants.ConstraintTypes;
import xgenerator.converter.OracleDataTypeConverter;
import xgenerator.converter.PropertyTypeConverter;
import xgenerator.converter.DataTypeConverter;
import xgenerator.jdbc.JdbcContext;
import xgenerator.model.FieldMetadata;
import xgenerator.util.NotationUtils;
import xgenerator.util.StringUtils;

public class MetadataDaoOracleImpl extends MetadataDaoJdbcAdapter {
	
	/**
	 * OracleDataTypeConverter
	 */
	private final DataTypeConverter dataTypeConverter = new OracleDataTypeConverter();
	
	/**
	 * JavaTypeConverter
	 */
	private PropertyTypeConverter propertyTypeConverter;

	/**
	 * <p>
	 * Title：获取所有表
	 * </p>
	 * @author <a href="mailto:shushanlee@msn.com">liss</a>
	 * @param dataSourceName
	 * @return
	 * @see xgenerator.dao.impl.MetadataDaoJdbcAdapter#getTables(java.lang.String)
	 */
	public Vector<String> getTables(String dataSourceName) {
		Vector<String> tables = new Vector<String>();
		
		if (StringUtils.isBlank(dataSourceName)) {
			return tables;
		}
		
		if(MOCK) {
			tables.add("EAP_USERS");
			tables.add("EAP_ROLES");
			tables.add("EAP_MENUS");
			return tables;
		}
		
		JdbcContext jdbcContext = JdbcContext.getJdbcContext(dataSourceName);
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = jdbcContext.currentConnection();
			stmt = conn.createStatement();
			String sql = "SELECT T.TABLE_NAME FROM USER_TABLES T ORDER BY T.TABLE_NAME";
			//System.out.println(sql);
			rs = stmt.executeQuery(sql);
			
			while(rs.next()) {
				tables.add(rs.getString("TABLE_NAME"));
			}
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage(), e);
		} finally {
			JdbcContext.close(stmt, rs);
		}
		
		return tables;
	}

	/**
	 * <p>
	 * Title：获取表注释
	 * </p>
	 * @author <a href="mailto:shushanlee@msn.com">liss</a>
	 * @param dataSourceName
	 * @param tableName
	 * @return
	 * @see xgenerator.dao.impl.MetadataDaoJdbcAdapter#getTableComments(java.lang.String, java.lang.String)
	 */
	public String getTableComments(String dataSourceName, String tableName) {
		String tableComments = "";
		
		if(MOCK) {
			tableComments = "用户管理";
			return tableComments;
		}
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcContext.getJdbcContext(dataSourceName).currentConnection();
			stmt = conn.createStatement();
			String sql = "SELECT T.COMMENTS FROM USER_TAB_COMMENTS T WHERE T.TABLE_NAME='" + tableName + "'";
			//System.out.println(sql);
			rs = stmt.executeQuery(sql);
			
			if(rs.next()) {
				tableComments = rs.getString("COMMENTS");
			}
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage(), e);
		} finally {
			JdbcContext.close(stmt, rs);
		}
		
		return tableComments;
	}
	
	/**
	 * <p>
	 * Title：获取所有列
	 * </p>
	 * @author <a href="mailto:shushanlee@msn.com">liss</a>
	 * @param dataSourceName
	 * @param tableName
	 * @return
	 * @see xgenerator.dao.impl.MetadataDaoJdbcAdapter#getTableColumns(java.lang.String, java.lang.String)
	 */
	public Vector<String> getTableColumns(String dataSourceName, String tableName) {
		Vector<String> tableColumns = new Vector<String>();
		if (null == tableName || "".equals(tableName)) {
			return tableColumns;
		}
		
		if(MOCK) {
			tableColumns.add("USER_ID");
			tableColumns.add("USER_NAME");
			tableColumns.add("USER_AGE");
			return tableColumns;
		}
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcContext.getJdbcContext(dataSourceName).currentConnection();
			stmt = conn.createStatement();
			String sql = "SELECT T.COLUMN_NAME FROM USER_TAB_COLUMNS T WHERE T.TABLE_NAME='" + tableName + "' ORDER BY T.COLUMN_ID";
			//System.out.println(sql);
			rs = stmt.executeQuery(sql);
			
			while(rs.next()) {
				tableColumns.add(rs.getString("COLUMN_NAME"));
			}
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage(), e);
		} finally {
			JdbcContext.close(stmt, rs);
		}
		
		return tableColumns;
	}
	
	/**
	 * <p>
	 * Title：获取列元数据
	 * </p>
	 * @author <a href="mailto:shushanlee@msn.com">liss</a>
	 * @param dataSourceName
	 * @param tableName
	 * @return
	 * @see xgenerator.dao.impl.MetadataDaoJdbcAdapter#getTableColumnMetadata(java.lang.String, java.lang.String)
	 */
	public List<FieldMetadata> getTableColumnMetadata(String dataSourceName, String tableName) {
		List<FieldMetadata> fieldMetadatas = new ArrayList<FieldMetadata>();
		
		if(MOCK) {
			Object[][] data = { { "用户名", "USER_NAME", "用户名", "VARCHAR", new Integer(256), null, false, "", true, "PRIMARY KEY" },
					{ "密码", "PASSWORD", "密码", "VARCHAR", new Integer(4), null, false, null, true, "FOREIGN KEY" },
					{ "创建时间", "CREATE_DATE", "创建时间", "DATE", null, null, true, "select sysdate() from dual", false, null },
					{ "年龄", "AGE", "年龄", "NUMBER", new Integer(10), null, true, null, false, null },
					{ "存款", "MONEY", "存款", "FLOAT", new Integer(10), new Integer(5), true, new Float(0.00000F), false, null },
					{ "备注", "DESCRIPTION", "备注", "CLOB", new Integer(1000), null, true, null, false, null } };

			for (int i = 0; i < data.length; i++) {
				FieldMetadata oneFieldMetadata = new FieldMetadata();

				oneFieldMetadata.setDisplayName(String.valueOf(data[i][0]));
				String columnName = String.valueOf(data[i][1]);
				oneFieldMetadata.setColumnName(columnName);
				oneFieldMetadata.setPropertyName(NotationUtils.lowerCamelCase(columnName));
				oneFieldMetadata.setComments(String.valueOf(data[i][2]));
				String dataType = String.valueOf(data[i][3]);
				int dataLength = null != data[i][4] ? (Integer) data[i][4] : 0;
				int dataPrecision = null != data[i][5] ? (Integer) data[i][5] : 0;
				int dataScale = 3;
				String sqlType = this.dataTypeConverter.convert(dataType, dataLength, dataPrecision, dataScale);
				oneFieldMetadata.setSqlDataType(sqlType);
				oneFieldMetadata.setDataLength(dataLength);
				oneFieldMetadata.setDataPrecision(dataPrecision);
				oneFieldMetadata.setDataScale(dataScale);
				String propertyType = this.getPropertyTypeConverter().convert(sqlType);
				oneFieldMetadata.setPropertyType(propertyType);
				oneFieldMetadata.setNullable((Boolean) data[i][6]);
				oneFieldMetadata.setDataDefault(null != data[i][7] ? "" + data[i][7] : null);
				oneFieldMetadata.setKeyField((Boolean) data[i][8]); // 是否为键
				oneFieldMetadata.setConstraintType(null != data[i][9] ? (String) data[i][9] : null); // 键类型

				fieldMetadatas.add(oneFieldMetadata);
			}
			
			return fieldMetadatas;
		}
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcContext.getJdbcContext(dataSourceName).currentConnection();
			stmt = conn.createStatement();
			//String sql = "SELECT A.*, B.* FROM (SELECT S.COMMENTS, T.COLUMN_ID, T.COLUMN_NAME, T.DATA_TYPE, T.DATA_LENGTH, T.DATA_PRECISION, T.DATA_SCALE, T.NULLABLE, T.DEFAULT_LENGTH, T.DATA_DEFAULT, T.CHAR_LENGTH FROM USER_COL_COMMENTS S, USER_TAB_COLUMNS T WHERE S.TABLE_NAME = T.TABLE_NAME AND S.COLUMN_NAME = T.COLUMN_NAME AND T.TABLE_NAME = '" + tableName + "') A, (SELECT U.COLUMN_NAME CONSTRAINT_COLUMN_NAME, U.POSITION, C.CONSTRAINT_NAME, C.CONSTRAINT_TYPE, C.TABLE_NAME, C.R_CONSTRAINT_NAME, C.DELETE_RULE, C.INDEX_NAME FROM USER_CONS_COLUMNS U, USER_CONSTRAINTS C WHERE U.CONSTRAINT_NAME = C.CONSTRAINT_NAME AND U.OWNER = C.OWNER AND U.TABLE_NAME = C.TABLE_NAME AND C.CONSTRAINT_TYPE IN ('P', 'R') AND U.TABLE_NAME = '" + tableName + "') B WHERE A.COLUMN_NAME = B.CONSTRAINT_COLUMN_NAME(+) ORDER BY A.COLUMN_ID";
			String sql = "SELECT A.*, B.* FROM (SELECT S.COMMENTS, S.TABLE_NAME, T.COLUMN_ID, T.COLUMN_NAME, T.DATA_TYPE, T.DATA_LENGTH, T.DATA_PRECISION, T.DATA_SCALE, T.NULLABLE, T.DEFAULT_LENGTH, T.DATA_DEFAULT, T.CHAR_LENGTH FROM USER_COL_COMMENTS S, USER_TAB_COLUMNS T WHERE S.TABLE_NAME = T.TABLE_NAME AND S.COLUMN_NAME = T.COLUMN_NAME AND T.TABLE_NAME = '" + tableName + "') A, (SELECT U.COLUMN_NAME CONSTRAINT_COLUMN_NAME, U.POSITION, C.R_TABLE_NAME, C.R_COLUMN_NAME, C.CONSTRAINT_NAME, C.CONSTRAINT_TYPE, C.TABLE_NAME, C.R_CONSTRAINT_NAME, C.DELETE_RULE, C.INDEX_NAME FROM USER_CONS_COLUMNS U, (SELECT N.TABLE_NAME AS R_TABLE_NAME, N.CONSTRAINT_COLUMN_NAME AS R_COLUMN_NAME, M.* from (SELECT H.COLUMN_NAME AS CONSTRAINT_COLUMN_NAME, G.* from USER_CONSTRAINTS G, USER_CONS_COLUMNS H WHERE G.OWNER=H.OWNER AND G.TABLE_NAME=H.TABLE_NAME AND G.CONSTRAINT_NAME=H.CONSTRAINT_NAME AND G.CONSTRAINT_TYPE IN('P', 'R')) M, (SELECT H.COLUMN_NAME AS CONSTRAINT_COLUMN_NAME, G.* from USER_CONSTRAINTS G, USER_CONS_COLUMNS H WHERE G.OWNER=H.OWNER AND G.TABLE_NAME=H.TABLE_NAME AND G.CONSTRAINT_NAME=H.CONSTRAINT_NAME AND G.CONSTRAINT_TYPE IN('P', 'R')) N where M.R_CONSTRAINT_NAME=N.CONSTRAINT_NAME(+) AND M.TABLE_NAME='" + tableName + "' AND M.CONSTRAINT_TYPE IN('P', 'R')) C WHERE U.OWNER = C.OWNER AND U.TABLE_NAME = C.TABLE_NAME AND U.CONSTRAINT_NAME = C.CONSTRAINT_NAME AND U.COLUMN_NAME = C.CONSTRAINT_COLUMN_NAME AND C.CONSTRAINT_TYPE IN ('P', 'R') AND U.TABLE_NAME = '" + tableName + "') B WHERE A.TABLE_NAME = B.TABLE_NAME(+) AND A.COLUMN_NAME = B.CONSTRAINT_COLUMN_NAME(+) ORDER BY A.COLUMN_ID";
//			System.out.println(sql);
			rs = stmt.executeQuery(sql);
			
			FieldMetadata oneFieldMetadata = null;
			while(rs.next()) {
				oneFieldMetadata = new FieldMetadata();
				
				oneFieldMetadata.setColumnName(rs.getString("COLUMN_NAME"));
				oneFieldMetadata.setComments(rs.getString("COMMENTS"));
				oneFieldMetadata.setDisplayName(rs.getString("COMMENTS"));
				String dataType = rs.getString("DATA_TYPE");
				int dataLength = rs.getInt("DATA_LENGTH");
				int dataPrecision = rs.getInt("DATA_PRECISION");
				int dataScale = rs.getInt("DATA_SCALE");
				String sqlType = this.dataTypeConverter.convert(dataType, dataLength, dataPrecision, dataScale);
				oneFieldMetadata.setSqlDataType(sqlType);
				oneFieldMetadata.setDataLength(dataLength);
				oneFieldMetadata.setDataPrecision(dataPrecision);
				oneFieldMetadata.setDataScale(dataScale);
				String nullableFlag = rs.getString("NULLABLE");
				if("Y".equals(nullableFlag)) {
					oneFieldMetadata.setNullable(true);
				} else {
					oneFieldMetadata.setNullable(false);
				}
				oneFieldMetadata.setDefaultLength(rs.getInt("DEFAULT_LENGTH"));
				oneFieldMetadata.setDataDefault(rs.getString("DATA_DEFAULT"));
				oneFieldMetadata.setCharLength(rs.getInt("CHAR_LENGTH"));
				
				oneFieldMetadata.setPropertyName(NotationUtils.lowerCamelCase(rs.getString("COLUMN_NAME")));
				String propertyType = this.getPropertyTypeConverter().convert(sqlType);
				oneFieldMetadata.setPropertyType(propertyType);
				
				String constraintType = rs.getString("CONSTRAINT_TYPE");  // 约束类型
				if(StringUtils.isNotBlank(constraintType)) {
					oneFieldMetadata.setKeyField(true); // 是否为键
				} else {
					oneFieldMetadata.setKeyField(false); // 是否为键
				}
				if("P".equals(constraintType)) {
					oneFieldMetadata.setConstraintType(ConstraintTypes.PK_TYPE);
				} else if("R".equals(constraintType)) {
					oneFieldMetadata.setConstraintType(ConstraintTypes.FK_TYPE);
					oneFieldMetadata.setReferencingTable(rs.getString("R_TABLE_NAME"));
					oneFieldMetadata.setReferencingColumn(rs.getString("R_COLUMN_NAME"));
					oneFieldMetadata.setDeleteRule(rs.getString("DELETE_RULE"));
				} else if("C".equals(constraintType)) {
					oneFieldMetadata.setConstraintType(ConstraintTypes.CHECK_TYPE);
				}
				fieldMetadatas.add(oneFieldMetadata);
			}
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage(), e);
		} finally {
			JdbcContext.close(stmt, rs);
		}
		
		return fieldMetadatas;
	}

	/**
	 * <p>
	 * Title：策略模式，设置属性类型转换策略
	 * </p>
	 * @param propertyTypeConverter
	 * @see xgenerator.dao.MetadataDao#setPropertyTypeConverter(xgenerator.converter.PropertyTypeConverter)
	 */
	public void setPropertyTypeConverter(PropertyTypeConverter propertyTypeConverter) {
		this.propertyTypeConverter = propertyTypeConverter;
	}
	
	/**
	 * @return the propertyTypeConverter
	 */
	public PropertyTypeConverter getPropertyTypeConverter() {
		return this.propertyTypeConverter;
	}

}
